package cn.group_06.bookmanager.dao;

import cn.group_06.bookmanager.model.cg;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

public class tjDao {



	public ResultSet listAllsp(Connection con)throws Exception{
		StringBuffer sb=new StringBuffer("select t.sl,c.* from ( select a.id,sum(b.sl) sl  from sp  a left join  cgzib b on  a.id = b.spbh  group by a.id) t \n" +
				"\n" +
				"left join   sp  c   on  t.id = c.id \n ");


		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}

	public ResultSet listAllcg(Connection con)throws Exception{
		StringBuffer sb=new StringBuffer("select  c.ygbh ,sum(sl) sl,sum(zj) zj  from  cgzb  c    GROUP BY c.ygbh ");


		PreparedStatement pstmt=(PreparedStatement) con.prepareStatement(sb.toString());
		return pstmt.executeQuery();
	}

}
